package com.jemmyee.pizza.web.controller;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.jemmyee.core.utils.DateUtils;
import com.jemmyee.core.utils.ReportUtils;
import com.jemmyee.pizza.service.IApplyService;
import com.jemmyee.pizza.service.IStatisticService;
import com.jemmyee.pizza.statistic.ApplyStatistic;
import com.jemmyee.pizza.web.Constants;
import com.jemmyee.pizza.web.bean.ApplyStatisticBean;
import com.jemmyee.pizza.web.bean.DailyStatisticBean;
import com.jemmyee.pizza.web.bean.ExcerptStatisticBean;

/**
 * @author qiuchao
 * 2011-3-5
 */
@Controller
@RequestMapping("/admin/statistic")
public class StatisticController {
	@Autowired
	private IStatisticService statisticService;
	@Autowired
	private IApplyService applyService ;
	
	@RequestMapping("/apply_statistic")
	public ModelAndView applyStatistic(@ModelAttribute ApplyStatisticBean bean,HttpServletRequest request){
		ApplyStatisticBean applyStatisticBean = new ApplyStatisticBean();
		String applyStartTime = "";
		String applyEndTime = "";
		String preExcerptStartTime = "";
		String preExcerptEndTime = "";
		String dredgeStartTime = "";
		String dredgeEndTime = "";
		if(bean != null){
			applyStatisticBean = bean;
			request.getSession().setAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN, applyStatisticBean);
			if(applyStatisticBean.getStartTime()==null){
				String date = DateUtils.parseDateToString(new Date());
				date = date.substring(0, date.lastIndexOf("-")) + "-01"+ " 00:00:00";
				applyStatisticBean.setStartTime(DateUtils.parseStringToDate(date));
			}
			if(applyStatisticBean.getEndTime()==null){
				applyStatisticBean.setEndTime(new Date());
			}
		}else if (request.getSession().getAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN) != null) {
			applyStatisticBean = (ApplyStatisticBean) request.getSession().getAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN);
		}
		
		if(applyStatisticBean.getStartTime()!=null){
			applyStartTime = " and shenqing_date >= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(applyStatisticBean.getStartTime()))+"'";
			preExcerptStartTime = " and s_date >= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(applyStatisticBean.getStartTime()))+"'";
			dredgeStartTime = " and shenqing_date >= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(applyStatisticBean.getStartTime()))+"'";
		}
		if(applyStatisticBean.getEndTime()!=null){
			applyEndTime = " and shenqing_date <= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(applyStatisticBean.getEndTime()))+"'";
			preExcerptEndTime = " and s_date <= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(applyStatisticBean.getEndTime()))+"'";
			dredgeEndTime = " and shenqing_date <= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(applyStatisticBean.getEndTime()))+"'";
		}
		ModelAndView mv = new ModelAndView();
		
		String sqlString = "select count(*) as quantity, sum(init_price) as total_price, init_price as unit_price from milestone.dbo.apply where init_price is not null "+applyStartTime+applyEndTime+" group by init_price"; 
		String sqlString_1 = "SELECT s_from as detail_name, COUNT(*) as quantity, SUM(s_num) AS total_gas_quantity, SUM(s_money) AS total_price FROM milestone.dbo.pre_excerpt WHERE s_from IS NOT NULL "+preExcerptStartTime+preExcerptEndTime+" GROUP BY s_from";
		String sqlString_2 = "SELECT meter_name as detail_name, COUNT(*) AS quantity, open_price as unit_price, SUM(open_price) AS total_price, meter_type as about_door FROM milestone.dbo.apply WHERE meter_name IS NOT NULL AND open_price IS NOT NULL "+dredgeStartTime+dredgeEndTime+" GROUP BY meter_name, open_price, meter_type ORDER BY meter_name";
		
		mv.addObject("asList", statisticService.getApplyStatistic(sqlString));
		mv.addObject("pesList", statisticService.getPreExcerptStatistic(sqlString_1));
		mv.addObject("dsList", statisticService.getDredgeStatistic(sqlString_2));
		
		mv.addObject("applyStatisticBean", applyStatisticBean);
		mv.setViewName("/admin/statistic/apply_statistic");
		return mv;
	}
	
	@RequestMapping("/excerpt_statistic")
	public ModelAndView excerptStatistic(@ModelAttribute ExcerptStatisticBean bean,HttpServletRequest request){
		ExcerptStatisticBean excerptStatisticBean = new ExcerptStatisticBean();
		String year = "";
		
		if(bean != null){
			excerptStatisticBean = bean;
			request.getSession().setAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN, excerptStatisticBean);
		}else if (request.getSession().getAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN) != null) {
			excerptStatisticBean = (ExcerptStatisticBean) request.getSession().getAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN);
		}
		
		if(excerptStatisticBean.getYear()!=null && !excerptStatisticBean.getYear().equals("")){
			year = " and p.s_date like '%"+excerptStatisticBean.getYear()+"%'";
		}else{
			String date = DateUtils.parseDateToString(new Date());
			year = " and p.s_date like '%"+date.substring(0, 4)+"%'";
			excerptStatisticBean.setYear(date.substring(0, 4));
		}
		
		ModelAndView mv = new ModelAndView();
		
		String sqlString = "select mingxi,sum(yi) as yi,sum(er) as er,sum(san) as san,sum(si) as si,sum(wu) as wu,sum(liu) as liu," +
			"sum(qi) as qi,sum(ba) as ba,sum(jiu) as jiu,sum(shi) as shi,sum(shiyi) as shiyi,sum(shier) as shier " +
			"from(select (a.meter_name+convert(varchar(20),a.meter_type)) as mingxi," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '01' then sum(p.s_money) end as 'yi'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '02' then sum(p.s_money) end as 'er'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '03' then sum(p.s_money) end as 'san'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '04' then sum(p.s_money) end as 'si'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '05' then sum(p.s_money) end as 'wu'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '06' then sum(p.s_money) end as 'liu'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '07' then sum(p.s_money) end as 'qi'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '08' then sum(p.s_money) end as 'ba'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '09' then sum(p.s_money) end as 'jiu'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '10' then sum(p.s_money) end as 'shi'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '11' then sum(p.s_money) end as 'shiyi'," +
			"case substring(convert(nvarchar(20),p.s_date,120),6,2) when '12' then sum(p.s_money) end as 'shier' " +
			"from milestone.dbo.pre_excerpt p left join milestone.dbo.apply a on p.u_id=a.apply_id where " +
			"a.meter_name is not null and a.meter_type is not null "+year+" group by " +
			"(a.meter_name+convert(varchar(20),a.meter_type)),substring(convert(nvarchar(20),s_date,120),6,2)) s " +
			"group by mingxi"; 
		
		mv.addObject("esList", statisticService.getExcerptStatistic(sqlString));
		
		mv.addObject("excerptStatisticBean", excerptStatisticBean);
		mv.setViewName("/admin/statistic/excerpt_statistic");
		return mv;
	}
	
	@RequestMapping("/cost_statistic")
	public ModelAndView costStatistic(String year){
		
		ModelAndView mv = new ModelAndView();
		if(year == null || "".equals(year)){
			String date = DateUtils.parseDateToString(new Date());
			year = date.substring(0, 4);
		}
		
		
		if(year != null && !"".equals(year)){
			String rowSql = "select type from v_cost where 1 = 1 and " + ReportUtils.dateType("handle_date", 0) + " = '" + year + "' group by type" ;
			String colSql = "select " + ReportUtils.dateType("handle_date", 1) + " from v_cost where 1 = 1 and " + ReportUtils.dateType("handle_date", 0) + " = '" + year + "' group by " + ReportUtils.dateType("handle_date", 1) ;
			String sql = "select type," + ReportUtils.dateType("handle_date", 1) + ",sum(price) from v_cost where 1 = 1 and " + ReportUtils.dateType("handle_date", 0) + " = '" + year + "' group by type," + ReportUtils.dateType("handle_date", 1);
			/*
			String inquirySql = "SELECT " + inquiryType(inquiryType) + ", " + dateType(dateType) + ", COUNT(*) FROM apply where 1 = 1 and startdate is not null and " + inquiryType(inquiryType) + " is not null " + dateEx(dateType(dateType),startDate,endDate) + " GROUP BY " + inquiryType(inquiryType) + ", " + dateType(dateType) ;
			
			String rowTitleSql = "select " + inquiryType(inquiryType) + " from apply where 1 = 1 and " + inquiryType(inquiryType) + " is not null " + dateEx(dateType(dateType),startDate,endDate) + " group by " + inquiryType(inquiryType) ;
			
			String colTitleSql = "select " + dateType(dateType) + " from apply where 1 = 1 and startdate is not null " + dateEx(dateType(dateType),startDate,endDate) + " group by " + dateType(dateType) ;
			
			*/
			List<List<String>> rowResult = applyService.queryByJDBC(rowSql, 1) ;
			
			List<List<String>> colResult = applyService.queryByJDBC(colSql, 1) ;
			if(colResult != null && colResult.size() > 0)colResult = ReportUtils.monthList(year) ;
			
			List<List<String>> result = applyService.queryByJDBC(sql, 3) ;
			
			List<List<String>> viewResult = ReportUtils.reconfigurationForReport(result, rowResult, colResult, "0") ;
			
			mv.addObject("result", viewResult) ;
			mv.addObject("year", year) ;
		}
		
		mv.setViewName("/admin/statistic/cost_statistic") ;
		return mv;
	}

	@RequestMapping("/daily_statistic")
	public ModelAndView daily_statistic(@ModelAttribute DailyStatisticBean bean,HttpServletRequest request){
		DailyStatisticBean dailyStatisticBean = new DailyStatisticBean();
		String applyStartTime = "";
		String applyEndTime = "";
		String preExcerptStartTime = "";
		String preExcerptEndTime = "";
		if(bean != null){
			dailyStatisticBean = bean;
			request.getSession().setAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN, dailyStatisticBean);
			if(dailyStatisticBean.getStartTime()==null){
				String date = DateUtils.parseDateToString(new Date());
				date = date.substring(0, date.lastIndexOf("-")) + "-01"+ " 00:00:00";
				dailyStatisticBean.setStartTime(DateUtils.parseStringToDate(date));
			}
			if(dailyStatisticBean.getEndTime()==null){
				dailyStatisticBean.setEndTime(new Date());
			}
		}else if (request.getSession().getAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN) != null) {
			dailyStatisticBean = (DailyStatisticBean) request.getSession().getAttribute(Constants.SESSION_PAGE_SYS_QUERY_BEAN);
		}
		
		if(dailyStatisticBean.getStartTime()!=null){
			applyStartTime = " and shenqing_date >= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(dailyStatisticBean.getStartTime()))+"'";
			preExcerptStartTime = " and s_date >= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(dailyStatisticBean.getStartTime()))+"'";
		}
		if(dailyStatisticBean.getEndTime()!=null){
			applyEndTime = " and shenqing_date <= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(dailyStatisticBean.getEndTime()))+"'";
			preExcerptEndTime = " and s_date <= '"+DateUtils.formatDateTime(DateUtils.parseDateToString(dailyStatisticBean.getEndTime()))+"'";
		}
		ModelAndView mv = new ModelAndView();
		
		StringBuffer sb = new StringBuffer();
		
		sb.append("	SELECT SUM(shisan)           AS shisan, 															   ");
		sb.append("	       SUM(ershi)            AS ershi,                                                                 ");
		sb.append("	       SUM(ershisan)         AS ershisan,															   ");
		sb.append("	       SUM(quantity)         AS quantity,  															   ");
		sb.append("	       n_date, 																						   ");
		sb.append("	       SUM(tingyuhuwai)      AS tingyuhuwai,                                                      	   ");
		sb.append("	       SUM(tingyuhunei)      AS tingyuhunei,                                        				   ");
		sb.append("	       SUM(lanyanhuwai)      AS lanyanhuwai,                                                           ");
		sb.append("	       SUM(lanyanhunei)      AS lanyanhunei,                                                           ");
		sb.append("	       SUM(shanchenghuwai)   AS shanchenghuwai,                                                        ");
		sb.append("	       SUM(shanchenghunei)   AS shanchenghunei,                                                        ");
		sb.append("	       SUM(tianxinhuwai)     AS tianxinhuwai,                                                          ");
		sb.append("	       SUM(tianxinhunei)     AS tianxinhunei,                                                          ");
		sb.append("	       SUM(tingyuhuwai_1)    AS tingyuhuwai_1, 														   ");
		sb.append("	       SUM(tingyuhunei_1)    AS tingyuhunei_1,                                                         ");
		sb.append("	       SUM(lanyanhuwai_1)    AS lanyanhuwai_1,                                                         ");
		sb.append("	       SUM(lanyanhunei_1)    AS lanyanhunei_1,                                                         ");
		sb.append("	       SUM(shanchenghuwai_1) AS shanchenghuwai_1,                                                      ");
		sb.append("	       SUM(shanchenghunei_1) AS shanchenghunei_1,                                                      ");
		sb.append("	       SUM(tianxinhuwai_1)   AS tianxinhuwai_1,                                                        ");
		sb.append("	       SUM(tianxinhunei_1)   AS tianxinhunei_1 		                                                   ");
		sb.append("	FROM   (SELECT shisan,  	                                                                           ");
		sb.append("	               ershi,     	                                                                           ");
		sb.append("	               ershisan,        	                                                                   ");
		sb.append("	               quantity,                                                                               ");
		sb.append("	               CASE                                                                                    ");
		sb.append("	                 WHEN t.n_date IS NULL THEN p.n_date 												   ");
		sb.append("	                 ELSE t.n_date                                                                         ");
		sb.append("	               END AS n_date,                                                                          ");
		sb.append("	               tingyuhuwai,                                                                            ");
		sb.append("	               tingyuhunei,                                                                            ");
		sb.append("	               lanyanhuwai,                                                                            ");
		sb.append("	               lanyanhunei,                                                                            ");
		sb.append("	               shanchenghuwai,                                                                         ");
		sb.append("	               shanchenghunei,                                                                         ");
		sb.append("	               tianxinhuwai,                                                                           ");
		sb.append("	               tianxinhunei,                                                                           ");
		sb.append("	               tingyuhuwai_1,                                                                          ");
		sb.append("	               tingyuhunei_1,                                                                          ");
		sb.append("	               lanyanhuwai_1,                                                                          ");
		sb.append("	               lanyanhunei_1,                                                                          ");
		sb.append("	               shanchenghuwai_1,                                                                       ");
		sb.append("	               shanchenghunei_1,                                                                       ");
		sb.append("	               tianxinhuwai_1,                                                                         ");
		sb.append("	               tianxinhunei_1                                                                          ");
		sb.append("	        FROM  (SELECT n_date,                                                                          ");
		sb.append("	                      SUM(shisan)         AS shisan,                                                   ");
		sb.append("	                      SUM(ershi)          AS ershi,                                                    ");
		sb.append("	                      SUM(ershisan)       AS ershisan,                                                 ");
		sb.append("	                      SUM(quantity)       AS quantity,                                                 ");
		sb.append("	                      SUM(tingyuhuwai)    AS tingyuhuwai,                                              ");
		sb.append("	                      SUM(tingyuhunei)    AS tingyuhunei,                                              ");
		sb.append("	                      SUM(lanyanhuwai)    AS lanyanhuwai,                                              ");
		sb.append("	                      SUM(lanyanhunei)    AS lanyanhunei,                                              ");
		sb.append("             SUM(shanchenghuwai) AS shanchenghuwai,                                                     ");
		sb.append("	                      SUM(shanchenghunei) AS shanchenghunei,                                           ");
		sb.append("	                      SUM(tianxinhuwai)   AS tianxinhuwai,                                             ");
		sb.append("	                      SUM(tianxinhunei)   AS tianxinhunei                                              ");
		sb.append("	               FROM  (SELECT shisan,                                                                   ");
		sb.append("	                             ershi,                                                                    ");
		sb.append("	                             ershisan,                                                                 ");
		sb.append("	                             quantity,                                                                 ");
		sb.append("	                             CASE                                                                      ");
		sb.append("	                               WHEN s.n_date IS NULL THEN a.n_date                                     ");
		sb.append("	                               ELSE s.n_date                                                           ");
		sb.append("	                             END AS n_date,                                                            ");
		sb.append("	                             tingyuhuwai,                                                              ");
		sb.append("	                             tingyuhunei,                                                              ");
		sb.append("	                             lanyanhuwai,                                                              ");
		sb.append("	                             lanyanhunei,                                                              ");
		sb.append("	                             shanchenghuwai,                                                           ");
		sb.append("	                             shanchenghunei,                                                           ");
		sb.append("	                             tianxinhuwai,                                                             ");
		sb.append("	                             tianxinhunei                                                              ");
		sb.append("	                      FROM  (SELECT Substring(CONVERT(NVARCHAR(20), s_date, 120)                       ");
		sb.append("	                                    , 0, 11                                                            ");
		sb.append("	                                    ) AS                                                               ");
		sb.append("	                                    n_date,                                                            ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '挺宇1'THEN SUM(p.s_money)                                  ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    tingyuhuwai,                                                       ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '挺宇0'THEN SUM(p.s_money)                                  ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    tingyuhunei,                                                       ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '蓝焰1'THEN SUM(p.s_money)                                  ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    lanyanhuwai,                                                       ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '蓝焰0'THEN SUM(p.s_money)                                  ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    lanyanhunei,                                                       ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '山城1'THEN SUM(p.s_money) 								   ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    shanchenghuwai,                                                    ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '山城0'THEN SUM(p.s_money)                                  ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    shanchenghunei,                                                    ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '天信1'THEN SUM(p.s_money)                                  ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    tianxinhuwai,                                                      ");
		sb.append("	                                    CASE(a.meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                    a.meter_type))                                                     ");
		sb.append("	                                      WHEN '天信0'THEN SUM(p.s_money)                                  ");
		sb.append("	                                    END                                                                ");
		sb.append("	                                    AS                                                                 ");
		sb.append("	                                    tianxinhunei                                                       ");
		sb.append("	                             FROM   milestone.dbo.pre_excerpt p                                        ");
		sb.append("	                                    LEFT JOIN milestone.dbo.APPLY a                                    ");
		sb.append("	                                      ON p.u_id = a.apply_id                                           ");
		sb.append("	                             WHERE  a.meter_name IS NOT NULL                                           ");
		sb.append("	                                    AND a.meter_type IS NOT NULL                                       ");
		sb.append(applyStartTime+applyEndTime+preExcerptStartTime+preExcerptEndTime);
		sb.append("	                             GROUP  BY( a.meter_name + CONVERT(VARCHAR(20),                            ");
		sb.append("	                                                       a.meter_type) ),                                ");
		sb.append("	                                      Substring(CONVERT(NVARCHAR(20), s_date,                          ");
		sb.append("	                                                120), 0,                                               ");
		sb.append("	                                      11)) s                                                           ");
		sb.append("	                            FULL JOIN(SELECT CASE init_price                                           ");
		sb.append("	                                               WHEN '1300.0'THEN SUM(init_price)                       ");
		sb.append("	                                             END                                                       ");
		sb.append("	                                             AS                                                        ");
		sb.append("	                            shisan,                                                                    ");
		sb.append("	                                             CASE init_price                                           ");
		sb.append("	                                               WHEN '2000.0'THEN SUM(init_price)                       ");
		sb.append("	                                             END                                                       ");
		sb.append("	                                             AS                                                        ");
		sb.append("	                            ershi,                                                                     ");
		sb.append("	                                             CASE init_price                                           ");
		sb.append("	                                               WHEN '2300.0'THEN SUM(init_price)                       ");
		sb.append("	                                             END                                                       ");
		sb.append("	                                             AS                                                        ");
		sb.append("	                            ershisan,                                                                  ");
		sb.append("	                                             COUNT(*)                                                  ");
		sb.append("	                                             AS                                                        ");
		sb.append("	                            quantity,                                                                  ");
		sb.append("	                                             Substring(CONVERT(NVARCHAR(20),                           ");
		sb.append("	                                                       shenqing_date,                                  ");
		sb.append("	                                                       120), 0,                                        ");
		sb.append("	                                             11) AS                                                    ");
		sb.append("	                            n_date                                                                     ");
		sb.append("	                                      FROM   milestone.dbo.APPLY                                       ");
		sb.append("	                                      WHERE  init_price IS NOT NULL                                    ");
		sb.append(applyStartTime+applyEndTime);
		sb.append("	                                      GROUP  BY init_price,                                            ");
		sb.append("	                                                Substring(CONVERT(NVARCHAR(20),                        ");
		sb.append("	                                                          shenqing_date,                               ");
		sb.append("	                                                          120),                                        ");
		sb.append("	                                                0, 11))                                                ");
		sb.append("	                            a                                                                          ");
		sb.append("	                              ON s.n_date = a.n_date) s                                                ");
		sb.append("	               GROUP  BY n_date) t                                                                     ");
		sb.append("	              FULL JOIN (SELECT n_date,                                                                ");
		sb.append("	                                SUM(tingyuhuwai)    AS tingyuhuwai_1,                                  ");
		sb.append("	                                SUM(tingyuhunei)    AS tingyuhunei_1,                                  ");
		sb.append("	                                SUM(lanyanhuwai)    AS lanyanhuwai_1,                                  ");
		sb.append("	                                SUM(lanyanhunei)    AS lanyanhunei_1,                                  ");
		sb.append("	                                SUM(shanchenghuwai) AS shanchenghuwai_1,                               ");
		sb.append("	                                SUM(shanchenghunei) AS shanchenghunei_1,                               ");
		sb.append("	                                SUM(tianxinhuwai)   AS tianxinhuwai_1,                                 ");
		sb.append("	                                SUM(tianxinhunei)   AS tianxinhunei_1                                  ");
		sb.append("	                         FROM  (SELECT Substring(CONVERT(NVARCHAR(20),                                 ");
		sb.append("	                                                 shenqing_date,                                        ");
		sb.append("	                                                 120), 0,                                              ");
		sb.append("	                                       11) AS                                                          ");
		sb.append("	                                       n_date,                                                         ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type))                                                    ");
		sb.append("	                                         WHEN '挺宇1'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              tingyuhuwai,                                             ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type))                                                    ");
		sb.append("	                                         WHEN '挺宇0'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              tingyuhunei,                                             ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type))                                                    ");
		sb.append("	                                         WHEN '蓝焰1'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              lanyanhuwai,                                             ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type))                                                    ");
		sb.append("	                                         WHEN '蓝焰0'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              lanyanhunei,                                             ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type)) 												   ");
		sb.append("	                                         WHEN '山城1'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              shanchenghuwai,                                          ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type))                                                    ");
		sb.append("	                                         WHEN '山城0'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              shanchenghunei,                                          ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type))                                                    ");
		sb.append("	                                         WHEN '天信1'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              tianxinhuwai,                                            ");
		sb.append("	                                       CASE(meter_name + CONVERT(VARCHAR(20),                          ");
		sb.append("	                                       meter_type))                                                    ");
		sb.append("	                                         WHEN '天信0'THEN COUNT(*)                                     ");
		sb.append("	                                       END                                                             ");
		sb.append("	                                       AS                                                              ");
		sb.append("	                                              tianxinhunei                                             ");
		sb.append("	                                FROM   milestone.dbo.APPLY                                             ");
		sb.append(" where 1=1 "+applyStartTime+applyEndTime);
		sb.append("	                                GROUP  BY( meter_name + CONVERT(VARCHAR(20),                           ");
		sb.append("	                                                        meter_type                                     ");
		sb.append("	                                                        ) ),                                           ");
		sb.append("	                                         Substring(CONVERT(NVARCHAR(20),                               ");
		sb.append("	                                                   shenqing_date,                                      ");
		sb.append("	                                                   120),                                               ");
		sb.append("	                                         0, 11)) s                                                     ");
		sb.append("	                         GROUP  BY s.n_date) p                                                         ");
		sb.append("	                ON t.n_date = p.n_date)s                                                               ");
		sb.append("	GROUP  BY n_date                                                                                       ");
		sb.append("	ORDER  BY n_date DESC                                                                                  ");

		mv.addObject("dsList", statisticService.getDailyStatistic(sb.toString()));
		mv.addObject("dailyStatisticBean", dailyStatisticBean);
		mv.setViewName("/admin/statistic/daily_statistic");
		return mv;
	}
}
